import pymysql


class UseMysql:

    def __init__(self):
        self.conn = None
        self.cursor = None
        self.data = None

    def main(self):
        self.connect()
        self.select()
        self.close()

    @staticmethod
    def connect():
        """
        :param host:
        :param user:
        :param passwd:
        :param db:
        :param port:
        :param charset:
        :return:
        """

        # connect method
        """
        """

        # Connection
        """
        class Connection(builtins.object)
         |  Connection(*, user=None, password='', host=None, dbs=None, unix_socket=None, port=0, charset='', 
                       sql_mode=None, read_default_file=None, conv=None, use_unicode=True, client_flag=0, 
                       cursorclass=<class 'pymysql.cursors.Cursor'>, init_command=None, connect_timeout=10, 
                       read_default_group=None, autocommit=False, local_infile=False, max_allowed_packet=16777216, 
                       defer_connect=False, auth_plugin_map=None, read_timeout=None, write_timeout=None, 
                       bind_address=None, binary_prefix=False, program_name=None, server_public_key=None, ssl=None, 
                       ssl_ca=None, ssl_cert=None, ssl_disabled=None, ssl_key=None, ssl_verify_cert=None, 
                       ssl_verify_identity=None, compress=None, named_pipe=None, passwd=None, db=None)
         |
         |  Representation of a socket with a mysql server.
         |
         |  The proper way to get an instance of this class is to call
         |  connect().
         |
         |  Establish a connection to the MySQL dbs. Accepts several
         |  arguments:
         |
         |  :param host: Host where the dbs server is located
         |  :param user: Username to log in as
         |  :param password: Password to use.
         |  :param dbs: Database to use, None to not use a particular one.
         |  :param port: MySQL port to use, default is usually OK. (default: 3306)
         |  :param bind_address: When the client has multiple network interfaces, specify
         |      the interface from which to connect to the host. Argument can be
         |      a hostname or an IP address.
         |  :param unix_socket: Optionally, you can use a unix socket rather than TCP/IP.
         |  :param read_timeout: The timeout for reading from the connection in seconds (default: None - no timeout)
         |  :param write_timeout: The timeout for writing to the connection in seconds (default: None - no timeout)
         |  :param charset: Charset you want to use.
         |  :param sql_mode: Default SQL_MODE to use.
         |  :param read_default_file:
         |      Specifies  my.cnf file to read these parameters from under the [client] section.
         |  :param conv:
         |      Conversion dictionary to use instead of the default one.
         |      This is used to provide custom marshalling and unmarshalling of types.
         |      See converters.
         |  :param use_unicode:
         |      Whether or not to default to unicode strings.
         |      This option defaults to true.
         |  :param client_flag: Custom flags to send to MySQL. Find potential values in constants.CLIENT.
         |  :param cursorclass: Custom cursor class to use.
         |  :param init_command: Initial SQL statement to run when connection is established.
         |  :param connect_timeout: Timeout before throwing an exception when connecting.
         |      (default: 10, min: 1, max: 31536000)
         |  :param ssl:
         |      A dict of arguments similar to mysql_ssl_set()'s parameters.
         |  :param ssl_ca: Path to the file that contains a PEM-formatted CA certificate
         |  :param ssl_cert: Path to the file that contains a PEM-formatted client certificate
         |  :param ssl_disabled: A boolean value that disables usage of TLS
         |  :param ssl_key: Path to the file that contains a PEM-formatted private key for the client certificate
         |  :param ssl_verify_cert: Set to true to check the validity of server certificates
         |  :param ssl_verify_identity: Set to true to check the server's identity
         |  :param read_default_group: Group to read from in the configuration file.
         |  :param autocommit: Autocommit mode. None means use server default. (default: False)
         |  :param local_infile: Boolean to enable the use of LOAD DATA LOCAL command. (default: False)
         |  :param max_allowed_packet: Max size of packet sent to server in bytes. (default: 16MB)
         |      Only used to limit size of "LOAD LOCAL INFILE" data packet smaller than default (16KB).
         |  :param defer_connect: Don't explicitly connect on construction - wait for connect call.
         |      (default: False)
         |  :param auth_plugin_map: A dict of plugin names to a class that processes that plugin.
         |      The class will take the Connection object as the argument to the constructor.
         |      The class needs an authenticate method taking an authentication packet as
         |      an argument.  For the dialog plugin, a prompt(echo, prompt) method can be used
         |      (if no authenticate method) for returning a string from the user. (experimental)
         |  :param server_public_key: SHA256 authentication plugin public key value. (default: None)
         |  :param binary_prefix: Add _binary prefix on bytes and bytearray. (default: False)
         |  :param compress: Not supported
         |  :param named_pipe: Not supported
         |  :param db: **DEPRECATED** Alias for dbs.
         |  :param passwd: **DEPRECATED** Alias for password.
         |
         |  See `Connection <https://www.python.org/dev/peps/pep-0249/#connection-objects>`_ in the
         |  specification.
         |
         |  Methods defined here:
         |
         |  __del__ = _force_close(self)
         |
         |  __enter__(self)
         |
         |  __exit__(self, *exc_info)
         |
         |  __init__(self, *, user=None, password='', host=None, dbs=None, unix_socket=None, port=0, charset='', sql_mode=None, read_default_file=None, conv=None, use_unicode=True, client_f
        lag=0, cursorclass=<class 'pymysql.cursors.Cursor'>, init_command=None, connect_timeout=10, read_default_group=None, autocommit=False, local_infile=False, max_allowed_packet=16777216, de
        fer_connect=False, auth_plugin_map=None, read_timeout=None, write_timeout=None, bind_address=None, binary_prefix=False, program_name=None, server_public_key=None, ssl=None, ssl_ca=None,
        ssl_cert=None, ssl_disabled=None, ssl_key=None, ssl_verify_cert=None, ssl_verify_identity=None, compress=None, named_pipe=None, passwd=None, db=None)
         |      Initialize self.  See help(type(self)) for accurate signature.
         |
         |  affected_rows(self)
         |
         |  autocommit(self, value)
         |
         |  begin(self)
         |      Begin transaction.
         |
         |  character_set_name(self)
         |
         |  close(self)
         |      Send the quit message and close the socket.
         |
         |      See `Connection.close() <https://www.python.org/dev/peps/pep-0249/#Connection.close>`_
         |      in the specification.
         |
         |      :raise Error: If the connection is already closed.
         |
         |  commit(self)
         |      Commit changes to stable storage.
         |
         |      See `Connection.commit() <https://www.python.org/dev/peps/pep-0249/#commit>`_
         |      in the specification.
         |
         |  connect(self, sock=None)
         |
         |  cursor(self, cursor=None)
         |      Create a new cursor to execute queries with.
         |
         |      :param cursor: The type of cursor to create; one of :py:class:`Cursor`,
         |          :py:class:`SSCursor`, :py:class:`DictCursor`, or :py:class:`SSDictCursor`.
         |          None means use Cursor.
         |
         |  escape(self, obj, mapping=None)
         |      Escape whatever value you pass to it.
         |
         |      Non-standard, for internal use; do not use this in your applications.
         |
         |  escape_string(self, s)
         |
         |  get_autocommit(self)
         |
         |  get_host_info(self)
         |
         |  get_proto_info(self)
         |
         |  get_server_info(self)
         |
         |  insert_id(self)
         |
         |  kill(self, thread_id)
         |
         |  literal(self, obj)
         |      Alias for escape()
         |
         |      Non-standard, for internal use; do not use this in your applications.
         |
         |  next_result(self, unbuffered=False)
         |
         |  ping(self, reconnect=True)
         |      Check if the server is alive.
         |
         |      :param reconnect: If the connection is closed, reconnect.
         |      :raise Error: If the connection is closed and reconnect=False.
         |
         |  query(self, sql, unbuffered=False)
         |      # The following methods are INTERNAL USE ONLY (called from Cursor)
         |
         |  rollback(self)
         |      Roll back the current transaction.
         |
         |      See `Connection.rollback() <https://www.python.org/dev/peps/pep-0249/#rollback>`_
         |      in the specification.
         |
         |  select_db(self, db)
         |      Set current db.
         |
         |      :param db: The name of the db.
         |
         |  set_charset(self, charset)
         |
         |  show_warnings(self)
         |      Send the "SHOW WARNINGS" SQL command.
         |
         |  thread_id(self)
         |      # _mysql support
         |
         |  write_packet(self, payload)
         |      Writes an entire "mysql packet" in its entirety to the network
         |      adding its length and sequence number.
         |
         |  ----------------------------------------------------------------------
         |  Data descriptors defined here:
         |
         |  __dict__
         |      dictionary for instance variables (if defined)
         |
         |  __weakref__
         |      list of weak references to the object (if defined)
         |
         |  open
         |      Return True if the connection is open
         |
         |  ----------------------------------------------------------------------
         |  Data and other attributes defined here:
         |
         |  DataError = <class 'pymysql.err.DataError'>
         |      Exception raised for errors that are due to problems with the
         |      processed data like division by zero, numeric value out of range,
         |      etc.
         |
         |  DatabaseError = <class 'pymysql.err.DatabaseError'>
         |      Exception raised for errors that are related to the
         |      dbs.
         |
         |  Error = <class 'pymysql.err.Error'>
         |      Exception that is the base class of all other error exceptions
         |      (not Warning).
         |
         |  IntegrityError = <class 'pymysql.err.IntegrityError'>
         |      Exception raised when the relational integrity of the dbs
         |      is affected, e.g. a foreign key check fails, duplicate key,
         |      etc.
         |
         |  InterfaceError = <class 'pymysql.err.InterfaceError'>
         |      Exception raised for errors that are related to the dbs
         |      interface rather than the dbs itself.
         |
         |  InternalError = <class 'pymysql.err.InternalError'>
         |      Exception raised when the dbs encounters an internal
         |      error, e.g. the cursor is not valid anymore, the transaction is
         |      out of sync, etc.
         |
         |  NotSupportedError = <class 'pymysql.err.NotSupportedError'>
         |      Exception raised in case a method or dbs API was used
         |      which is not supported by the dbs, e.g. requesting a
         |      .rollback() on a connection that does not support transaction or
         |      has transactions turned off.
         |
         |  OperationalError = <class 'pymysql.err.OperationalError'>
         |      Exception raised for errors that are related to the dbs's
         |      operation and not necessarily under the control of the programmer,
         |      e.g. an unexpected disconnect occurs, the data source name is not
         |      found, a transaction could not be processed, a memory allocation
         |      error occurred during processing, etc.
         |
         |  ProgrammingError = <class 'pymysql.err.ProgrammingError'>
         |      Exception raised for programming errors, e.g. table not found
         |      or already exists, syntax error in the SQL statement, wrong number
         |      of parameters specified, etc.
         |
         |  Warning = <class 'pymysql.err.Warning'>
         |      Exception raised for important warnings like data truncations
         |      while inserting, etc.
                """

        # create database in mysql
        """
        mysql> create database students;
        mysql> use students;
        mysql> create table score (id varchar(10), name varchar(20), math int, art float);
        mysql> insert into score (id, name, math, art) values ('211001', 'MengBoli', 120, 56.2); 
        mysql> insert into score (id, name, math, art) values ('211002', 'DengTong', 110, 86.7);
        mysql> commit;
        mysql> grant select on students.score to 'syntax'@'127.0.0.1';        
        """
        # 连接数据库
        conn = pymysql.connect(
                host='127.0.0.1',
                user='syntax',
                passwd='syntax',
                db='students',
                port=3306,
                charset='utf8'
                )
        # 获取游标
        cursor = conn.cursor()
        cursor.execute("""select * from score""")
        result = cursor.fetchall()
        print(
            " >>> conn = pymysql.connect(\n"
            " ...        host='127.0.0.1',\n"
            " ...        user='syntax',\n"
            " ...        passwd='syntax',\n"
            " ...        db='students',\n"
            " ...        port=3306,\n"
            " ...        charset='utf8'\n"
            " ...   )\n"
            " >>> cursor = conn.cursor()\n"
            ' >>> cursor.execute("""select * from score""")\n'
            f" >>> cursor.fetchall()\n"
            f"{result}"
        )
        conn.close()

    def select(self, sql='', fetch_mode='all', fetch_size=10):
        try:
            self.cursor.execute(sql)
            self.cursor.commit()
            if fetch_mode == 'all':
                self.data = self.cursor.fetchall()
                return
            elif fetch_mode == 'many':
                self.data = self.cursor.fetchmany(fetch_size)
            elif fetch_mode == 'onde':
                self.data = self.cursor.fetchone()
            else:
                self.data = self.cursor.fetchall()
        except pymysql.DataError:
            self.cursor.rollback()
            self.data = None
        print(self.data)

    def close(self):
        self.cursor.close()
        self.conn.colse()
